package Actions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

public class reviewerAction extends Action{
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		
		forms.reviewerHomeform rf = (forms.reviewerHomeform) form;

		String url = "jdbc:postgresql://localhost/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt2 = null;
		Statement stmt3 = null;
		Statement stmt4 = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;
		String instruc = null;
		rf.resetNames();
		// Registering Postgresql JDBC driver
        try {
        	
			Class.forName("org.postgresql.Driver");
	        // Open a connection to the database        
	        conn = DriverManager.getConnection(
	                "jdbc:postgresql://localhost:5432/Applications", "postgres",
	                "postgres");
	        // Create the statement
	        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    					ResultSet.CONCUR_UPDATABLE);
	        stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    					ResultSet.CONCUR_UPDATABLE);
	        stmt3 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);        
	        stmt4 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE); 
	        
	        rs = stmt.executeQuery("SELECT app_id FROM Application WHERE rev_id ="
	        						+"(SELECT revID FROM users WHERE user_name = '" 
	        						+ request.getSession().getAttribute("uname") + "')");
	        while(rs.next()){
	        	int app_id = rs.getInt("app_id");
	        	rs2 = stmt2.executeQuery("SELECT distinct NameID FROM MainTable WHERE AppID ='"
	        							+app_id+"'");
	        	while(rs2.next()){
	        		rs3 = stmt3.executeQuery("SELECT * FROM NAME WHERE NameID ='"
	        								+rs2.getInt("NameID")+"'");
	   
	        		while(rs3.next()){
		        		rs4 = stmt4.executeQuery("SELECT user_name FROM users WHERE APP ='"
								+rs2.getInt("NameID")+"'");
		        		rs4.next();
		        		System.out.println("fdadfa"+rs4.getString("user_name"));
	        			rf.updateNames(new forms.nameform(rs3.getString("First"),
	        						rs3.getString("Middle"),rs3.getString("Last"),
	        						rs4.getString("user_name")));
	        		}
	        		
	        	}
	        }
	        
	        
	        
        } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		
		System.out.println(request.getSession().getAttribute("uname"));
		
	    	return mapping.findForward("start");
	}
}